package com.lz.sports.db

import androidx.room.*
import androidx.sqlite.db.SupportSQLiteQuery
import com.lz.sports.bean.*

@Dao
interface ExamineeDao {

    //插入考生表
    @Insert
    fun insertExaminee(examineeList: List<Examinee>)

    //添加考生表
    @Insert
    fun addExaminee(examinee: Examinee)

    //插入考生关联项目成绩表
    @Insert
    fun insertExamResult(examResultList: List<ExamResult>)

    //添加考生关联项目成绩表
    @Insert
    fun addExamResult(examResult: ExamResult)

    @Update
    fun updateExamResult(examResultList: List<ExamResult>)

    @Update
    fun updateExamSignList(signInList: List<ExamSignIn>)

    @Update
    fun updateExamResultList(signInList: List<ExamResult>)

    @Update
    fun updateExamSureList(sureList: List<ExamSure>)

    //插入成绩确认
    @Insert
    fun insertExamSure(examSure: List<ExamSure>)

    //查询考生是否存在
    @Query("select COUNT(*) from Examinee e left join ExamResult er on e.id = er.examineeId where er.projectId = :projectId and e.examPlanId = :examPlanId and e.examAreaId = :examAreaId and e.examSiteId = :examSiteId and (e.idCard = :idCard or e.examineesNo = :examineesNo)")
    fun hasExaminee(examPlanId: Int, projectId: Int, examAreaId: Int, examSiteId: Int, examineesNo: String, idCard: String): Long

    //查找考生对应的考试项目成绩
    @Query("select * from ExamResult where examineeId = :examineeId and examPlanId = :examPlanId and examAreaId = :examAreaId and examSiteId = :examSiteId")
    fun examResultList(examPlanId: Int, examAreaId: Int, examSiteId: Int, examineeId: Int): List<ExamResult>

    //查询考生带项目成绩表
    @Transaction
    @Query("select * from Examinee where examPlanId = :examPlanId and examAreaId = :examAreaId and examSiteId = :examSiteId and idCard = :idCard")
    fun examineeWithResults(examPlanId: Int, examAreaId: Int, examSiteId: Int, idCard: String): ExamineeWithResult?

    //查询考生成绩表带考生信息, 筛选条件
    @Transaction
    @RawQuery
    fun examResultWithInfo(query: SupportSQLiteQuery): List<ExamResultWithInfo>

    //清空考生表
    @Query("delete from Examinee")
    fun clearExaminee()

    //清空考生成绩表
    @Query("delete from ExamResult")
    fun clearExamResult()

    //清空考生签到
    @Query("delete from ExamSignIn")
    fun clearExamSign()

    //清空考生确认表
    @Query("delete from ExamSure")
    fun clearExamSure()

    //检录未考的学生数量
    @Query("select COUNT(*) from ExamResult where examPlanId = :examPlanId and projectId = :projectId and examAreaId = :examAreaId and examSiteId = :examSiteId and inspectStatus = -1")
    fun examineeUnInspectCount(examPlanId: Int, projectId: Int, examAreaId: Int, examSiteId: Int): Long

    //查找学生信息筛选项目,考区考点
    @Query("select * from Examinee where examPlanId = :examPlanId and examAreaId = :examAreaId and examSiteId = :examSiteId and idCard = :idCard")
    fun findExamineeByIdCard(examPlanId: Int, examAreaId: Int, examSiteId: Int, idCard: String): Examinee?

    //查找考生项目成绩
    @Query("select * from ExamResult where examineeId = :examineeId and examPlanId = :examPlanId and projectId = :projectId and examAreaId = :examAreaId and examSiteId = :examSiteId")
    fun findExamResult(examineeId: Int, examPlanId: Int, projectId: Int, examAreaId: Int, examSiteId: Int): ExamResult?

    //考生签到
    @Insert
    fun signIn(signIn: ExamSignIn): Long

    //查找签到记录
    @Query("select * from ExamSignIn where examineeId = :examineeId and examPlanId = :examPlanId and examAreaId = :examAreaId and examSiteId = :examSiteId order by signInTime desc limit 1")
    fun findSignIn(examineeId: Int, examPlanId: Int, examAreaId: Int, examSiteId: Int): ExamSignIn?

    //更新考生检录状态
    @Query("update ExamResult set inspectStatus = 0, lackStatus = -1 where examPlanId = :examPlanId and projectId = :projectId and examAreaId = :examAreaId and examSiteId = :examSiteId and examineeId in (:examineeIds)")
    fun updateExamInspectStatus(examPlanId: Int, projectId: Int, examAreaId: Int, examSiteId: Int, examineeIds: List<Int>)

    //查询签到
    @Transaction
    @Query("select es.* from ExamSignIn es left join Examinee e on es.examineeId = e.id where es.examPlanId = :examPlanId and es.examAreaId = :examAreaId and es.examSiteId = :examSiteId and (e.studentName like '%' || :keyword || '%' or e.examineesNo like '%' || :keyword || '%' or e.idCard like '%' || :keyword || '%')")
    fun querySignIn(keyword: String, examPlanId: Int, examAreaId: Int, examSiteId: Int): List<ExamSignInWithInfo>

    @Transaction
    @Query("select * from ExamSignIn where examPlanId = :examPlanId and examAreaId = :examAreaId and examSiteId = :examSiteId")
    fun querySignIn(examPlanId: Int, examAreaId: Int, examSiteId: Int): List<ExamSignInWithInfo>

    //查询确认成绩
    @Transaction
    @Query("select es.* from ExamSure es left join Examinee e on es.examineeId = e.id where es.examPlanId = :examPlanId and es.examAreaId = :examAreaId and es.examSiteId = :examSiteId and (e.studentName like '%' || :keyword || '%' or e.examineesNo like '%' || :keyword || '%' or e.idCard like '%' || :keyword || '%')")
    fun querySure(keyword: String, examPlanId: Int, examAreaId: Int, examSiteId: Int): List<ExamSureWithInfo>

    @Transaction
    @Query("select * from ExamSure where examPlanId = :examPlanId and examAreaId = :examAreaId and examSiteId = :examSiteId")
    fun querySure(examPlanId: Int, examAreaId: Int, examSiteId: Int): List<ExamSureWithInfo>



    // ------------------------  验证照的时候要加上 是否为人脸验证的条件 ----------------------

    //查询所有未上传的签到
    @Query("select * from ExamSignIn where uploadStatus = -1")
    fun signUploadList(): List<ExamSignIn>

    //查询报考照未传
    @Query("select * from ExamSignIn where examPhotoUploadStatus = -1 and examType > 0 and uploadStatus = 0")
    fun signExamPhotoUploadList(): List<ExamSignIn>

    //查询身份证照未传
    @Query("select * from ExamSignIn where idCardPhotoUploadStatus = -1 and examType > 0 and uploadStatus = 0")
    fun signIdCardPhotoUploadList(): List<ExamSignIn>

    //查询现场照未传
    @Query("select * from ExamSignIn where scenePhotoUploadStatus = -1 and examType > 0 and uploadStatus = 0")
    fun signScenePhotoUploadList(): List<ExamSignIn>

    //查询未上传的最终成绩
    @Query("select * from ExamResult where uploadStatus = -1 and score >= 0")
    fun resultUploadList(): List<ExamResult>

    //查询未上传的缺考成绩
    @Query("select * from ExamResult where lackUploadStatus = -1 and lackStatus = 0")
    fun lackUploadList(): List<ExamResult>

    //查询未上传的成绩确认
    @Query("select * from ExamSure where uploadStatus = -1")
    fun sureUploadList(): List<ExamSure>

    //查询报考照未传
    @Query("select * from ExamSure where examPhotoUploadStatus = -1 and examType > 0 and uploadStatus = 0")
    fun sureExamPhotoUploadList(): List<ExamSure>

    //查询身份证照未传
    @Query("select * from ExamSure where idCardPhotoUploadStatus = -1 and examType > 0 and uploadStatus = 0")
    fun sureIdCardPhotoUploadList(): List<ExamSure>

    //查询现场照未传
    @Query("select * from ExamSure where scenePhotoUploadStatus = -1 and examType > 0 and uploadStatus = 0")
    fun sureScenePhotoUploadList(): List<ExamSure>

    //查询电子签名照未传
    @Query("select * from ExamSure where signaturePhotoUploadStatus = -1 and examType > 0 and uploadStatus = 0")
    fun sureSignaturePhotoUploadList(): List<ExamSure>


    @Query("select * from ExamResult")
    fun test(): List<ExamResult>

    @Query("select * from Examinee")
    fun test2(): List<Examinee>
}